package cz.acies.data.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cz.acies.data.DBConnector;
import cz.acies.data.persist.Country;

public class CountryDAO extends AbstractDAO
{
	public CountryDAO() {};
	
	public List<Country> selectAll() {
		List<Country> records =  new ArrayList<Country>();
		try {  
			String sql = "SELECT * FROM country";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			ResultSetHandler<List<Country>> handler = new BeanListHandler<Country>(Country.class);
			records = qr.query(sql, handler);
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}
	
	public Country selectRecordByCode(String code) {
		Country record =  new Country();
		try {
			String sql = "SELECT * FROM country WHERE country.code LIKE '"+code+"%'";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			ResultSetHandler<List<Country>> handler = new BeanListHandler<Country>(Country.class);
			List<Country> records = qr.query(sql, handler);
			if (!records.isEmpty()) {
				record = records.get(0);
			}			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return record;
	}

	public Country selectRecordByName(String name) {
		Country record =  new Country();
		try {
			String sql = "SELECT * FROM country WHERE country.name LIKE '"+name+"%'";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			ResultSetHandler<List<Country>> handler = new BeanListHandler<Country>(Country.class);
			List<Country> records = qr.query(sql, handler);
			if (!records.isEmpty()) {
				record = records.get(0);
			}			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return record;
	}

	public String insert(Country record) {
		String id = record.getId();
		try {
			if (id == null || id.trim().length() == 0) {
				
			}

			String sql = "INSERT INTO country (id, number, code, code2, name, dictCode) VALUES (" +
				"'"+record.getId()+"', "+
				"'"+record.getNumber()+"', "+
				"'"+record.getCode()+"', "+
				"'"+record.getCode2()+"', "+
				"'"+record.getName()+"', "+
				"'"+record.getDictCode()+"'"+
				"" +
			")";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			qr.update(sql);
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return id;
	}
}
